﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using Database;

//设置界面常用方法
namespace DoMain.SetDomain
{
    public class UserSetDomain
    {
        readonly DatabaseInfo _dbInfo = new DatabaseInfo();

        /// <summary>
        /// 获取数据库中的用户信息
        /// </summary>
        /// <returns></returns>
        public DataTable UserQuery()
        {
            string sql = "SELECT t1.*,t2.ROLENAME from USERS t1,ROLE t2 WHERE t1.ROLEID=t2.ID";
            return _dbInfo.TableDataQuery(sql, "USERS");
        }

        /// <summary>
        /// 查询数据库中的角色名
        /// </summary>
        /// <returns></returns>
        public List<string> RoleQuery()
        {
            string sql = "SELECT ROLENAME FROM ROLE ";
            var result = _dbInfo.ColumnDataQuery(sql, "ROLENAME");
            return result;
        }

        /// <summary>
        /// 查询用户名是否已存在 true已存在  false不存在
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public bool IsNameExist(string userName)
        {
            string sql = string.Format("SELECT NAME FROM USERS WHERE NAME='{0}' ", userName);
            var result = _dbInfo.ColumnDataQuery(sql, "NAME");
            return result.Count != 0;
        }

        /// <summary>
        /// 增加用户
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="roleName"></param>
        /// <param name="filePath"></param>
        public string AddUser(string userName, string roleName, string filePath)
        {
            try
            {
                string userId = Guid.NewGuid().ToString("N");
                string roleSql = string.Format("SELECT ID FROM ROLE WHERE ROLENAME='{0}'", roleName);
                var roleIds = _dbInfo.ColumnDataQuery(roleSql, "ID");
                string roleId = roleIds.First();
                string insertSql = string.Format("INSERT INTO USERS VALUES ('{0}','{1}','123','{2}',@ImageList)",
                    userId, userName,roleId);

                FileStream fs = new FileStream(filePath, FileMode.Open);
                BinaryReader br = new BinaryReader(fs);
                var imagebytes = br.ReadBytes(Convert.ToInt32(fs.Length));

                SqlCommand com = new SqlCommand(insertSql, _dbInfo.SqlConn);
                com.Parameters.Add("ImageList", SqlDbType.Image);
                com.Parameters["ImageList"].Value = imagebytes;
                com.ExecuteNonQuery();
                return "增加用户成功。";
            }
            catch (Exception ex)
            {
                return "增加用户失败。";
            }
        }

        /// <summary>
        /// 为用户添加电子签名图片
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="filePath"></param>
        public string AddUserImage(string userName,string filePath)
        {
            try
            {
                FileStream fs = new FileStream(filePath, FileMode.Open);
                BinaryReader br = new BinaryReader(fs);
                var imagebytes = br.ReadBytes(Convert.ToInt32(fs.Length));
                string sql = string.Format("insert into USERS(SIGNATURE) values(@ImageList) where NAME = '{0}'",
                    userName);
                SqlCommand com = new SqlCommand(sql, _dbInfo.SqlConn);
                com.Parameters.Add("ImageList", SqlDbType.Image);
                com.Parameters["ImageList"].Value = imagebytes;
                com.ExecuteNonQuery();
                return "签名保存成功。";
            }
            catch (Exception)
            {
                return "签名保存失败。";
            }
        }

    }
}
